<?php
/**
 * Created by PhpStorm.
 * User: JSQ-SL
 * Date: 2018/11/19
 * Time: 15:37
 */

namespace app\index\table;

use think\Db;
use app\index\service\ExcelService;

class RateOutTable extends BaseTable
{
    protected static $view = 'V_RATE_OUT_INFO';
    protected static $table = 'RATE_OUT_INFO';
    protected static $pk = 'R_OUT_ID';
    protected static $configTable = 'RATE_OUT_CONFIG';

    static public function table(){
        return Db::table(static::$view);
    }

    static public function add($data){
        $data[self::$pk] = static::getNextId();
        Db::table(static::$table)->insert($data);
    }

    static public function update($where,$data){
        Db::table(static::$table)->where($where)->update($data);
    }

    static public function delete($where){
        return Db::table(static::$table)->where($where)->delete();
    }

    public static function getConfigAdd(){
        return Db::table(self::$configTable)->where(['ADD_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getConfigEdit(){
        return Db::table(self::$configTable)->where(['EDIT_FLAG'=>'1'])->order('ID ASC')->select();
    }
    public static function getConfigPrint(){
        return Db::table(self::$configTable)->where(['PRINT_FLAG'=>'1'])->order('ID ASC')->select();
    }

    public static function getNextId(){
        $year = date('Y');
        $numMaxRecord = Db::table(static::$table)
            ->field('max(SUBSTR('.self::$pk.',5,4)) as NUM')
            ->where([['SUBSTR('.self::$pk.',1,4)','=',$year]])
            ->fetchSql(true)
            ->find();
        $numMaxRecord = Db::query($numMaxRecord);
        $num = $numMaxRecord[0]['NUM'];
        if ($num == NULL) {
            $num = 0;
        }
        $num++;
        return $year.sprintf("%04d", $num);
    }

    public static function exportExcel($data,$fileName){

        $fieldList = [
            ['title'=>'学院','field'=>'SHORTNAME'],
            ['title'=>'年份','field'=>'YEAR'],
            ['title'=>'在校生总数','field'=>'PT_NUM_ZX'],
            ['title'=>'博士生人数比例','field'=>'PT_RATE_BS'],
            ['title'=>'毕业生总数','field'=>'PT_NUM_BY'],
            ['title'=>'学生竞赛获奖数','field'=>'PT_NUM_JS'],
            ['title'=>'优秀博士论文数','field'=>'PT_NUM_YB'],
            ['title'=>'优秀硕士论文数','field'=>'PT_NUM_YS'],
            ['title'=>'初始就业率','field'=>'PT_RATE_JY'],
            ['title'=>'本科生读研比例','field'=>'PT_RATE_DY'],
            ['title'=>'出国留学率','field'=>'PT_RATE_OUT'],
            ['title'=>'出国交流学生人数比例','field'=>'PT_RATE_OUT_JL'],
            ['title'=>'在站博士后人数','field'=>'PT_NUM_BSH'],
            ['title'=>'国家级优秀教学成果奖数','field'=>'PT_NUM_GJ'],
            ['title'=>'省级优秀教学成果奖数','field'=>'PT_NUM_SJ'],
            ['title'=>'学术或科技（社科）著作数','field'=>'KX_NUM_ZZ'],
            ['title'=>'SCI、EI、SSCI、CPCI-S、CSSCI论文数','field'=>'KX_NUM_LW'],
            ['title'=>'Science&Nature上发表的论文数','field'=>'KX_NUM_SN'],
            ['title'=>'SCI学科影响因子前1/10的期刊发表论文数','field'=>'KX_NUM_SCI'],
            ['title'=>'国家级奖励数','field'=>'KX_NUM_GJ'],
            ['title'=>'省部级奖励数','field'=>'KX_NUM_SB'],
            ['title'=>'横向科研经费输入','field'=>'SH_FEE_HX'],
            ['title'=>'专利、技术转让/许可收入','field'=>'SH_FEE_ZL'],
            ['title'=>'信息咨询服务收入','field'=>'SH_FEE_XX'],
            ['title'=>'非学历教育收入','field'=>'SH_FEE_XL'],
            ['title'=>'实验室、大型仪器共享收入','field'=>'SH_FEE_SB'],
        ];

        $excel = new ExcelService();
        $excel->exportExcel($data,$fileName,$fieldList);
    }

    const RATE_OUT_FIELD_LIST = [
        "COLLEGE_ID","YEAR","PT_NUM_ZX","PT_RATE_BS","PT_NUM_BY","PT_NUM_JS","PT_NUM_YB","PT_NUM_YS","PT_RATE_JY","PT_RATE_DY","PT_RATE_OUT","PT_RATE_OUT_JL","PT_NUM_BSH","PT_NUM_GJ","PT_NUM_SJ","KX_NUM_ZZ","KX_NUM_LW","KX_NUM_SN","KX_NUM_SCI","KX_NUM_GJ","KX_NUM_SB","SH_FEE_HX","SH_FEE_ZL","SH_FEE_XX","SH_FEE_XL","SH_FEE_SB",
    ];
}